Paging, Subsort Recordset

This is a multi-part message in MIME format.

------=_NextPart_000_0079_01C596E6.96A3AA40
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

Can anyone point me to a code example where a recordset.absolutepage is =
sorted independantly of the original recordset. That is; if I populate =
an ADO recordset with an ORDER BY ListingDate clause, and I need to =
(subsort) recordset.absolutepage five of my paging on another field, say =
for example AskingPrice, I'm stuck.

My goal is for my users to be able to page through my recordset, sort a =
page if desired, and continue through the recordset. The records on any =
page are sorted, and the same records are displayed, just in a new =
(subsort) oder.=20

I'm not using the data controls, just plain ASP...

Thanks

Kev

Current code below...






<%
check_security(2)
%>
<%

If Session("UserID") =3D "" Then
Response.Redirect "Login.asp"
End if
=20
Dim rsItems, strSort
=20
strSort =3D request("sortby")
=20
strSQL =3D "SELECT Item.ItemID, Users.CompanyName, =
EquipmentTypes.EquipName, Item.EquipmentType, Item.LoadSize, =
Item.PickupDate, Item.SPCountry, Item.SPState, " & _
"Item.SPCity, Item.DPCountry, Item.DPState, Item.DPCity, =
Item.Description, Item.AskingPrice, Item.SellerID, Item.ListingDate, =
Item.ExpirationDate, Item.ItemStatus " & _
"FROM (Item INNER JOIN EquipmentTypes ON Item.EquipmentType =
=3D EquipmentTypes.EquipCode) LEFT JOIN Users ON Item.SellerID =3D =
Users.UserID " & _
"WHERE Item.ExpirationDate > #" & FormatDateTime(Now,2) & "# =
" & _
"AND ItemStatus =3D 'Active' "

If request("LoadSize") <> "" then
strSQL =3D strSQL & "AND LoadSize =3D '" & request("LoadSize") & "' =
"
End if
=20
If request("EquipmentType") <> "" then
strSQL =3D strSQL & "AND EquipmentType =3D '" & =
request("EquipmentType") & "' "
End if
=20
If request("SPCountry") <> "" then
strSQL =3D strSQL & "AND SPCountry =3D '" & request("SPCountry") & =
"' "
End if

If request("SPState") <> "" then
strSQL =3D strSQL & "AND SPState =3D '" & request("SPState") & "' "
End if

If request("DPCountry") <> "" then
strSQL =3D strSQL & "AND DPCountry =3D '" & request("DPCountry") & =
"' "
End if

If request("DPState") <> "" then
strSQL =3D strSQL & "AND DPState =3D '" & request("DPState") & "' "
End if

strSQL =3D strSQL & "ORDER BY PickupDate;"

page_no =3D request("page_no")
if page_no =3D "" then page_no =3D 1

Set rsItems =3D Server.CreateObject("ADODB.Recordset")
=20
if strSql <> "" then
rsItems.CursorLocation =3D 3
rsItems.CacheSize =3D 5
rsItems.Sort =3D "PickupDate, " & strSort
rsItems.Open strSql, objConn
if not rsItems.EOF then=20
rsItems.MoveFirst
rsItems.PageSize =3D 5
max_count =3D cInt(rsItems.PageCount)
num_recs =3D rsItems.RecordCount
rsItems.AbsolutePage =3D page_no
results =3D true
for each tmp in request.querystring
if tmp <> "page_no" AND tmp <> "sortby" then
request_string =3D request_string & tmp & "=3D" & =
request.querystring(tmp) & "&"
end if
next
else
results =3D false
rsItems.Close
end if
else
results =3D false
end if
rec_count =3D 0
%>

Re: Paging, Subsort Recordset

"Kevin" wrote in message
news:ey6nEFylFHA.664@TK2MSFTNGP10.phx.gbl...
> Can anyone point me to a code example where a recordset.absolutepage
> is sorted independantly of the original recordset. That is; if I populate
an
> ADO recordset with an ORDER BY ListingDate clause, and I need to
> (subsort) recordset.absolutepage five of my paging on another field, say
> for example AskingPrice, I'm stuck.

You will need to create a new recordset containing just the records from
page 5. There are two ways to do this. Which one you pick depends on how
many records are in the recordset.

a) Use the recordset's Clone method to create a clone of your recordset
(hopefully, you've previously disconnected the recordset and closed the
connection). Then use a loop to delete all the records from the clone, move
to page 5 in the original recordset, use a loop to write the records from
page 5 into the clone (using AddNew), and modify the Sort property of the
clone.

b) Create an ad hoc recordset using a loop to add the fields from the
original recordset to the Fields collection of the new recordset. Then use
AddNew as above to add the page 5 records to the new recordset.

Here is method a - this is untested air-code so it may contain typos or
syntax errors:

....
rsItems.Open strSql, objConn
set rsItems.ActiveConnection=Nothing
objConn.close: set objConn=nothing
set rsReSort=rsItems.Clone
do until rsResort.eof
rsResort.Delete
rsResort.MoveNext
loop
rsItems.AbsolutePage=5
do until rsItems.eof OR _
rsResort.RecordCount = rsItems.PageSize
rsResort.AddNew
for each fld in rsItems.Fields
rsResort(fld.Name)=fld.Value
next
rsResort.Update
rsItems.MoveNext
loop
rsResort.Sort = "..."

HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From header is
my spam trap, so I don't check it very often. You will get a quicker
response by posting to the newsgroup.